﻿using DocumentFormat.OpenXml.Spreadsheet;
using EasyCaching.Core;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Configuration;
using NP.BPMReportPlatform.AuthorizationUserManager.Dtos;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business.Reports;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using NP.BPMReportPlatform.Entities.BPM_Process;
using NP.BPMReportPlatform.Entities.NPPlatformManage;
using NP.BPMReportPlatform.Reports.OrderMaterialDetail.Dtos;
using NPOI.HSSF.Record;
using Org.BouncyCastle.Crypto.Engines;
using Serilog;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;

namespace NP.BPMReportPlatform.AuthorizationUserManager
{
    public class PlatformManageRoleService : BPMReportPlatformAppService, IPlatformManageRoleService
    {
        private readonly SqlSugarClient _dbNPPlatformManage;
        private string _tenantId;
        private readonly IEasyCachingProviderFactory _easyCachingProviderFactory;
        private readonly ILogger _logger;
        public PlatformManageRoleService(ISqlSugarAppService sqlSugarAppService, IConfiguration configuration,
            IEasyCachingProviderFactory easyCachingProviderFactory, ILogger logger)
        {
            _dbNPPlatformManage = sqlSugarAppService.GetNPPlatformManageInstance();
            _tenantId = configuration["BPM:TenantId"];
            _easyCachingProviderFactory = easyCachingProviderFactory;
            _logger = logger;
        }

        #region 角色信息

        /// <summary>
        /// 获取平台管理角色信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<PermissionsRoles>> GetPageList(PlatformManageRoleInput input)
        {
            var data = await GetDataList(input);
            //返回结果
            return new PagedResultDto<PermissionsRoles>()
            {
                TotalCount = data.Item1,
                Items = data.Item2
            };
        }

        private async Task<Tuple<int, List<PermissionsRoles>>> GetDataList(PlatformManageRoleInput input)
        {
            var sqlBuilder = new StringBuilder("SELECT * FROM \"public\".\"PermissionsRoles\" WHERE \"TenantId\"=@tenantId AND \"IsDeleted\"=FALSE ");
            var countSqlBuilder = new StringBuilder("SELECT COUNT(*) FROM \"public\".\"PermissionsRoles\" WHERE \"TenantId\"=@tenantId AND \"IsDeleted\"=FALSE ");
            var sqlParameters = new List<SugarParameter>()
            {
                new SugarParameter("@tenantId", Guid.Parse(_tenantId))
            };
            if (input.IsEnabled != null)
            {
                sqlBuilder.Append(" AND \"IsEnabled\"=@IsEnabled ");
                countSqlBuilder.Append(" AND \"IsEnabled\"=@IsEnabled ");
                sqlParameters.Add(new SugarParameter("@IsEnabled", input.IsEnabled));
            }
            if (input.IsPublic != null)
            {
                sqlBuilder.Append(" AND \"IsPublic\"=@IsPublic ");
                countSqlBuilder.Append(" AND \"IsPublic\"=@IsPublic ");
                sqlParameters.Add(new SugarParameter("@IsPublic", input.IsPublic));
            }
            if (!string.IsNullOrWhiteSpace(input.Name))
            {
                sqlBuilder.Append(" AND \"Name\" like @Name ");
                countSqlBuilder.Append(" AND \"Name\" like @Name ");
                sqlParameters.Add(new SugarParameter("@Name", $"%{input.Name}%"));
            }

            if (input.IsAll.HasValue && input.IsAll.Value)
            {
                sqlBuilder.Append(" ORDER BY \"CreationTime\",\"Id\" DESC ");
                var dataList = await _dbNPPlatformManage.Ado.SqlQueryAsync<PermissionsRoles>(sqlBuilder.ToString(), sqlParameters);
                return new Tuple<int, List<PermissionsRoles>>(dataList.Count(), dataList);
            }
            else
            {
                var totalCount = await _dbNPPlatformManage.Ado.SqlQueryAsync<int>(countSqlBuilder.ToString(), sqlParameters);
                sqlBuilder.Append(" ORDER BY \"CreationTime\",\"Id\" DESC ");
                sqlBuilder.Append($" LIMIT {input.PageSize} OFFSET {(input.PageIndex - 1) * input.PageSize} ");
                var dataList = await _dbNPPlatformManage.Ado.SqlQueryAsync<PermissionsRoles>(sqlBuilder.ToString(), sqlParameters);
                return new Tuple<int, List<PermissionsRoles>>(totalCount.FirstOrDefault(), dataList);
            }
        }

        /// <summary>
        /// 导出平台管理角色信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<PermissionsRoles>> ExportExcel(PlatformManageRoleInput input)
        {
            var data = await GetDataList(input);
            return data.Item2;
        }

        #endregion

        #region 已绑定角色用户信息

        /// <summary>
        /// 查询已绑定角色用户数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<PlatformManageRoleUserDto>> GetUserRoleList(PlatformManageRoleUserInput input)
        {
            var data = await GetRoleUserDataList(input);
            //返回结果
            return new PagedResultDto<PlatformManageRoleUserDto>()
            {
                TotalCount = data.Item1,
                Items = data.Item2
            };
        }

        private async Task<Tuple<int, List<PlatformManageRoleUserDto>>> GetRoleUserDataList(PlatformManageRoleUserInput input)
        {
            var sqlBuilder = new StringBuilder(@"SELECT r.""Id"" AS ""RelationId"",u.""Id"" AS ""UserId"",r.""RoleId"",u.""UserName"",u.""Name"",pr.""Name"" AS ""RoleName""
                FROM ""public"".""AbpUsers"" u
                JOIN ""public"".""PermissionsUserRoleRelations"" r ON u.""Id""=r.""UserId""
                JOIN ""public"".""PermissionsRoles"" pr on r.""RoleId""=pr.""Id""
                WHERE u.""IsDeleted""=FALSE AND r.""IsDeleted""=FALSE AND pr.""IsDeleted""=FALSE 
                AND r.""TenantId""=@tenantId AND pr.""TenantId""=@tenantId ");
            var countSqlBuilder = new StringBuilder(@"SELECT COUNT(*)
                FROM ""public"".""AbpUsers"" u
                JOIN ""public"".""PermissionsUserRoleRelations"" r ON u.""Id""=r.""UserId""
                JOIN ""public"".""PermissionsRoles"" pr on r.""RoleId""=pr.""Id""
                WHERE u.""IsDeleted""=FALSE AND r.""IsDeleted""=FALSE AND pr.""IsDeleted""=FALSE 
                AND r.""TenantId""=@tenantId AND pr.""TenantId""=@tenantId ");
            var sqlParameters = new List<SugarParameter>()
            {
                new SugarParameter("@tenantId", Guid.Parse(_tenantId))
            };
            if (input.RoleId != null)
            {
                sqlBuilder.Append(" AND r.\"RoleId\"=@RoleId ");
                countSqlBuilder.Append(" AND r.\"RoleId\"=@RoleId ");
                sqlParameters.Add(new SugarParameter("@RoleId", input.RoleId));
            }
            if (!string.IsNullOrWhiteSpace(input.UserName))
            {
                sqlBuilder.Append(" AND u.\"UserName\" = @UserName ");
                countSqlBuilder.Append(" AND u.\"UserName\" = @UserName ");
                sqlParameters.Add(new SugarParameter("@UserName", input.UserName));
            }
            if (!string.IsNullOrWhiteSpace(input.Name))
            {
                sqlBuilder.Append(" AND u.\"Name\" like @Name ");
                countSqlBuilder.Append(" AND u.\"Name\" like @Name ");
                sqlParameters.Add(new SugarParameter("@Name", $"%{input.Name}%"));
            }

            if (input.IsAll.HasValue && input.IsAll.Value)
            {
                sqlBuilder.Append(" ORDER BY r.\"CreationTime\",r.\"Id\" DESC ");
                var dataList = await _dbNPPlatformManage.Ado.SqlQueryAsync<PlatformManageRoleUserDto>(sqlBuilder.ToString(), sqlParameters);
                return new Tuple<int, List<PlatformManageRoleUserDto>>(dataList.Count(), dataList);
            }
            else
            {
                var totalCount = await _dbNPPlatformManage.Ado.SqlQueryAsync<int>(countSqlBuilder.ToString(), sqlParameters);
                sqlBuilder.Append(" ORDER BY r.\"CreationTime\",r.\"Id\" DESC ");
                sqlBuilder.Append($" LIMIT {input.PageSize} OFFSET {(input.PageIndex - 1) * input.PageSize} ");
                var dataList = await _dbNPPlatformManage.Ado.SqlQueryAsync<PlatformManageRoleUserDto>(sqlBuilder.ToString(), sqlParameters);
                return new Tuple<int, List<PlatformManageRoleUserDto>>(totalCount.FirstOrDefault(), dataList);
            }
        }

        /// <summary>
        /// 导出已绑定角色用户数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<PlatformManageRoleUserDto>> ExportRoleUserExcel(PlatformManageRoleUserInput input)
        {
            var data = await GetRoleUserDataList(input);
            return data.Item2;
        }

        /// <summary>
        /// 批量删除角色和用户的绑定关系
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public async Task DeleteRelations(PlatformManageRoleUserDeleteInput input)
        {
            if (input.RelationIds == null || !input.RelationIds.Any()) throw new ArgumentNullException("Input Parameters Is Null Or Empty");

            var sql = $"UPDATE \"public\".\"PermissionsUserRoleRelations\" SET \"IsDeleted\"=TRUE WHERE \"Id\" IN (@deleteIds)";
            await _dbNPPlatformManage.Ado.ExecuteCommandAsync(sql, new { deleteIds = input.RelationIds });

            var searchSql = $"SELECT \"UserId\" FROM \"public\".\"PermissionsUserRoleRelations\" WHERE \"IsDeleted\"=TRUE AND \"Id\" IN (@deleteIds)";
            var userIds = await _dbNPPlatformManage.Ado.SqlQueryAsync<Guid>(searchSql, new { deleteIds = input.RelationIds });
            await RemoveUserCache(userIds.Distinct().ToList());
        }

        /// <summary>
        /// 保存用户和角色的绑定关系，只能新增
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        /// <exception cref="Exception"></exception>
        public async Task BindUsers(PlatformManageBindUsersInput input)
        {
            if (input.UserIds == null || !input.UserIds.Any() || input.RoleId == Guid.Empty) throw new ArgumentNullException("Input Parameters Is Null Or Empty");

            var exsitUsers = await _dbNPPlatformManage.Ado.SqlQueryAsync<PlatformManageRoleUserDto>(
                @"SELECT * FROM ""public"".""PermissionsUserRoleRelations""
                WHERE ""IsDeleted""=FALSE AND ""TenantId""=@tenantId
                AND ""UserId"" IN (@userIds) AND ""RoleId""=@RoleId", new { tenantId = Guid.Parse(_tenantId), userIds = input.UserIds, input.RoleId });
            var exsitUserIds = exsitUsers.Select(x => x.UserId).ToList();
            var addUserIds = input.UserIds.Where(x => !exsitUserIds.Contains(x)).ToList();

            var isSuccessfull = false;
            try
            {
                _dbNPPlatformManage.BeginTran();
                foreach (var userId in addUserIds)
                {
                    _dbNPPlatformManage.Ado.ExecuteCommand(@"INSERT INTO ""public"".""PermissionsUserRoleRelations""
                        VALUES(gen_random_uuid(), now(),NULL,NULL,NULL,FALSE,NULL,NULL,@tenantId,@userId,@RoleId)",
                        new { tenantId = Guid.Parse(_tenantId), userId, input.RoleId });
                }
                _dbNPPlatformManage.CommitTran();

                isSuccessfull = true;
            }
            catch(Exception ex)
            {
                _dbNPPlatformManage.RollbackTran();
                throw new Exception(ex.Message);
            }

            if (isSuccessfull)
            {
                await RemoveUserCache(addUserIds.Distinct().ToList());
            }
        }

        #endregion

        #region 用户信息

        /// <summary>
        /// 获取平台管理用户信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<PlatformManageUserDto>> GetUserPageList(PlatformManageRoleUserInput input)
        {
            var data = await GetUserDataList(input);
            //返回结果
            return new PagedResultDto<PlatformManageUserDto>()
            {
                TotalCount = data.Item1,
                Items = data.Item2
            };
        }

        private async Task<Tuple<int, List<PlatformManageUserDto>>> GetUserDataList(PlatformManageRoleUserInput input)
        {
            var sqlBuilder = new StringBuilder("SELECT * FROM \"AbpUsers\" WHERE \"IsDeleted\"=FALSE ");
            var countSqlBuilder = new StringBuilder("SELECT COUNT(*) FROM \"AbpUsers\" WHERE \"IsDeleted\"=FALSE ");
            var sqlParameters = new List<SugarParameter>();
            if (!string.IsNullOrWhiteSpace(input.UserName))
            {
                sqlBuilder.Append(" AND \"UserName\" = @UserName ");
                countSqlBuilder.Append(" AND \"UserName\" = @UserName ");
                sqlParameters.Add(new SugarParameter("@UserName", input.UserName));
            }
            if (!string.IsNullOrWhiteSpace(input.Name))
            {
                sqlBuilder.Append(" AND \"Name\" like @Name ");
                countSqlBuilder.Append(" AND \"Name\" = @Name ");
                sqlParameters.Add(new SugarParameter("@Name", $"%{input.Name}%"));
            }

            if (input.IsAll.HasValue && input.IsAll.Value)
            {
                sqlBuilder.Append(" ORDER BY \"CreationTime\",\"Id\" DESC ");
                var dataList = await _dbNPPlatformManage.Ado.SqlQueryAsync<PlatformManageUserDto>(sqlBuilder.ToString(), sqlParameters);
                return new Tuple<int, List<PlatformManageUserDto>>(dataList.Count(), dataList);
            }
            else
            {
                var totalCount = await _dbNPPlatformManage.Ado.SqlQueryAsync<int>(countSqlBuilder.ToString(), sqlParameters);
                sqlBuilder.Append(" ORDER BY \"CreationTime\",\"Id\" DESC ");
                sqlBuilder.Append($" LIMIT {input.PageSize} OFFSET {(input.PageIndex - 1) * input.PageSize} ");
                var dataList = await _dbNPPlatformManage.Ado.SqlQueryAsync<PlatformManageUserDto>(sqlBuilder.ToString(), sqlParameters);
                return new Tuple<int, List<PlatformManageUserDto>>(totalCount.FirstOrDefault(), dataList);
            }
        }

        /// <summary>
        /// 导出平台管理用户信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<PlatformManageUserDto>> ExportUserExcel(PlatformManageRoleUserInput input)
        {
            var data = await GetUserDataList(input);
            return data.Item2;
        }

        #endregion

        #region 角色绑定权限

        /// <summary>
        /// 获取角色绑定权限树结构数据
        /// </summary>
        /// <returns></returns>
        public async Task<PlatformManageRoleMenuTreeData> GetAuthTreeData(Guid roleId)
        {
            var role = await _dbNPPlatformManage.Ado.SqlQueryAsync<PermissionsRoles>(
                "SELECT * FROM \"public\".\"PermissionsRoles\" WHERE \"TenantId\"=@tenantId AND \"IsDeleted\"=FALSE AND \"Id\"=@roleId",
                new { tenantId = Guid.Parse(_tenantId), roleId });
            if (role == null)
            {
                throw new Exception("角色信息未找到！");
            }

            var authData = await _dbNPPlatformManage.Ado.SqlQueryAsync<PermissionsRoleMenuRelations>(
                @"SELECT * FROM ""public"".""PermissionsRoleMenuRelations""
                WHERE ""IsDeleted""=FALSE AND ""RoleId""=@roleId AND ""TenantId""=@tenantId", new { roleId, tenantId = Guid.Parse(_tenantId) });

            var allData = await _dbNPPlatformManage.Ado.SqlQueryAsync<PermissionsMenus>(
                @"SELECT * FROM ""public"".""PermissionsMenus""
                WHERE ""IsDeleted""=FALSE AND ""IsEnabled""=TRUE AND ""TenantId""=@tenantId", new { tenantId = Guid.Parse(_tenantId) });
            var allDataList = allData.Select(x => new RoleMenuTree
            {
                id = x.Id,
                parentid = x.ParentId,
                name = x.Title,
                title = x.Title,                
            }).ToList();

            var allTreeData = BulidTree(allDataList, new List<RoleMenuTree>(), null, authData);

            return new PlatformManageRoleMenuTreeData { TreeData = allTreeData };
        }

        /// <summary>
        /// 使用递归方法建树
        /// </summary>
        private static List<RoleMenuTree> BulidTree(List<RoleMenuTree> treeNodes, List<RoleMenuTree> resps, Guid? pID, List<PermissionsRoleMenuRelations> authData)
        {
            resps = new List<RoleMenuTree>();
            List<RoleMenuTree> tempList = treeNodes.Where(c => c.parentid == pID).ToList();

            for (int i = 0; i < tempList.Count; i++)
            {
                RoleMenuTree node = new()
                {
                    id = tempList[i].id,
                    parentid = tempList[i].parentid,
                    name = tempList[i].title,
                    title = tempList[i].title,
                    @checked = !treeNodes.Any(x => x.parentid == tempList[i].id) && authData.Any(x => x.MenuId == tempList[i].id),
                };
                node.children = BulidTree(treeNodes, resps, node.id, authData);
                resps.Add(node);
            }

            return resps;
        }

        /// <summary>
        /// 导出授权导入模板
        /// </summary>
        /// <returns></returns>
        public async Task<List<ImportTemplateModel>> ExportTemplateExcel()
        {
            return new List<ImportTemplateModel>();
        }

        /// <summary>
        /// 导入授权数据
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task<ImportResultExt<ImportTemplateModel>> ImportUserRoleExcel(List<ImportTemplateModel> data)
        {
            var rst = new ImportResultExt<ImportTemplateModel>
            {
                Data = data
            };

            var insertDatas = new List<InsertBindUserRoleModel>();
            var index = 2;
            foreach (var model in data)
            {
                if (string.IsNullOrWhiteSpace(model.RoleName))
                {
                    rst.Exception = new Exception($"第{index}行，没有角色名称");
                    break;
                }
                if (string.IsNullOrWhiteSpace(model.UserName))
                {
                    rst.Exception = new Exception($"第{index}行，没有工号");
                    break;
                }
                var user = await _dbNPPlatformManage.Ado.SqlQueryAsync<PlatformManageUserDto>(
                    "SELECT * FROM \"AbpUsers\" WHERE \"IsDeleted\"=FALSE AND \"UserName\"=@UserName", new { UserName = model.UserName.Trim() });
                if (user == null || !user.Any())
                {
                    rst.Exception = new Exception($"第{index}行，用户信息未找到");
                    break;
                }
                var role = await _dbNPPlatformManage.Ado.SqlQueryAsync<PermissionsRoles>(
                    @"SELECT * FROM ""public"".""PermissionsRoles""
                    WHERE ""IsDeleted""=FALSE AND ""IsEnabled""=TRUE AND ""Name""=@RoleName 
                    AND ""TenantId""=@tenantId", new { model.RoleName, tenantId = Guid.Parse(_tenantId) });
                if (role == null || !role.Any())
                {
                    rst.Exception = new Exception($"第{index}行，角色信息未找到");
                    break;
                }
                var userRole = await _dbNPPlatformManage.Ado.SqlQueryAsync<PlatformManageRoleUserDto>(
                    @$"SELECT * FROM ""public"".""PermissionsUserRoleRelations""
                    WHERE ""IsDeleted""=FALSE AND ""TenantId""=@tenantId
                    AND ""UserId""=@userId AND ""RoleId""=@roleId",
                    new { tenantId = Guid.Parse(_tenantId), userId = user.First().Id, roleId = role.First().Id });
                if (userRole == null || !userRole.Any())
                {
                    insertDatas.Add(new InsertBindUserRoleModel
                    {
                        RoleId = role.First().Id,
                        UserId = user.First().Id
                    });
                }

                index++;
            }

            if (!rst.HasError)
            {
                var isSuccessfull = false;
                try
                {
                    _dbNPPlatformManage.BeginTran();

                    foreach (var insertData in insertDatas)
                    {
                        _dbNPPlatformManage.Ado.ExecuteCommand(@$"INSERT INTO ""public"".""PermissionsUserRoleRelations""
                        VALUES(gen_random_uuid(), now(),NULL,NULL,NULL,FALSE,NULL,NULL,@tenantId,@UserId,@RoleId)",
                        new { tenantId = Guid.Parse(_tenantId), insertData.UserId, insertData.RoleId });
                    }

                    _dbNPPlatformManage.CommitTran();

                    isSuccessfull = true;
                }
                catch (Exception ex)
                {
                    rst.Exception = new Exception(ex.Message);
                    _dbNPPlatformManage.RollbackTran();
                }

                if (isSuccessfull)
                {
                    await RemoveUserCache(insertDatas.Select(x => x.UserId).Distinct().ToList());
                }
            }

            var resp = new ImportResultExt<ImportTemplateModel>()
            {
                Exception = rst.Exception,
                RowErrors = rst.RowErrors,
                TemplateErrors = rst.TemplateErrors
            };
            return resp;
        }

        #endregion

        /// <summary>
        /// 删除缓存
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        private async Task RemoveUserCache(List<Guid> userIds)
        {
            try
            {
                var provider = _easyCachingProviderFactory.GetCachingProvider(PermissionCacheConsts.CacheProviderKey);
                var tenantId = Guid.Parse(_tenantId);
                userIds.ForEach(async userId =>
                {
                    await provider.RemoveByPrefixAsync(PermissionCacheConsts.UserPermissionsKey(tenantId, userId));
                    await provider.RemoveByPrefixAsync(PermissionCacheConsts.UserInterfacesKey(tenantId, userId));
                });
            }
            catch(Exception ex)
            {
                _logger.Error($"RemoveUserCache Error:{ex.Message}");
            }
        }
    }
}
